########################################################################
#  3account - personal finance data-base
#  Copyright (C) 2007,8,9 Ehud Ben-Reuven
#  udi@benreuven.com
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation version 2.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
########################################################################
"""Convert Citi N.A. (Citibank North America)
Portfolio (investment) Account Activity CSV file to flat--OFX-RDF and
load into Sesame server.
"""
from csv2flt import *
import re
import os
import time
from n3tagvalue import *

header = [
['Account Number','Memo','Price','Quantity','Activity Type','Check Number',
 'Total','Trade Date','Cusip']
]
line=['(?P<ACCTID>[\d\-A-Z]+)',
      strre('MEMO'),
      floatre('UNITPRICE'),
      floatre('UNITS'),
      strre('TRNTYPE'),
      "(99999)?",
      floatre('TOTAL'),
      '(?P<month>\d{2})/(?P<day>\d{2})/(?P<year>\d{4})',
      '#(((?P<SECID>[A-Z\d]{9})000)|\s*)',
      ]
footer=[ # This footer was added between 070501 and 070601
         [],
["<!-- -->"],
]

def flat(csvfile,fout,context=None,hashing=False):
    dtasof=time.strftime("%Y%m%d%H%M%S",time.gmtime(os.stat(csvfile).st_mtime))
    logging.info("DTASOF=%s"%dtasof)
    n3header(fout,"$Id$"[1:-1],context)
    h,t,f = readcsvtable(csvfile,header,line,footer,optional_footer=True)

    accttype='INVESTMENT'
    currency='USD'

    table_total=0
    accounts=[]
    for l in t:
        print >>fout,"a3:flatLine [ ",
        acctid=l['ACCTID']
        memo=l['MEMO']
        if l['SECID']:
            secid=l['SECID']
        else:
            secid='CASH='+currency
        dttrade = l['year']+l['month']+l['day']
        units=float(l['UNITS']) if l['UNITS'] else None
        if units==0: units=None
        unitprice=float(l['UNITPRICE']) if l['UNITPRICE'] else None	
        if unitprice==0: unitprice=None
        total=float(l['TOTAL'])	if l['TOTAL'] else None
        if total==0: total=None

        # TRNTYPE
        relsecid=None
        assetclass=None
        trntype=l['TRNTYPE']
        if secid.startswith('CASH='):
            if trntype=='DEPOSIT':
                assert unitprice==None
                assert units==None
                assert total>0
                trntype='CREDIT'
            elif trntype=='CASH JRNL':
                assert unitprice==None
                assert units==None
                assert total<0
                trntype='DEBIT'
            elif trntype=='FEE':
                assert unitprice==None
                assert units==None
                assert total<0
                trntype='DEBIT'
            else:
                logging.info('Unknown TRNTYPE %s'%trntype)
        else:
            if trntype=='BOUGHT':
                assert unitprice>0
                assert units>0
                assert units*unitprice<=-total+0.02 # rounding error
                trntype='BUY'
            elif trntype=='SOLD':
                assert unitprice>0
                assert units<0
                assert -units*unitprice>=total-0.03 # Rounding error
                trntype='SELL'
            elif trntype=='INTEREST':
                assert unitprice==None or unitprice==1 # interest from deposit
                assert units==None
                assert total>0
                trntype='INT'				
            elif trntype=='DIVIDEND':
                assert unitprice==None
                assert units==None
                assert total
                if total<0:
                    logging.info('%s','Negative dividend %f'%total)
                trntype='DIV'
            elif trntype=='CAPITAL GAIN':
                assert unitprice==None
                assert units==None
                assert total
                if total<0:
                    logging.info('%s','Negative dividend %f'%total)
                if memo.find('L/T')!=-1:
                    trntype='CGLONG'
                else:
                    trntype='CGSHORT'
            elif trntype=='REINVEST':
                if unitprice==None and units==None:
                    assert total<0
                    relsecid=secid
                    #TODO trntype='DEBIT'
                    #TODO secid='CASH='+currency
                else:
                    assert unitprice>0
                    assert units>0
                    assert not total # is 0 because it is always followed with a REINVEST transaction in current account
                    trntype='BUY'
            elif trntype=='REVERSAL':
                pass
            elif trntype=='CONVERSION':
                assert total==None
                assert unitprice==None
                trntype='TRANSFER'
            elif trntype=='WITHDRAWAL':
                if units:
                    assert unitprice==1
                    assert units<0
                    assert -units*unitprice>=total-0.01 # Rounding error
                trntype='DEBIT'
                assetclass='DEPOSIT'
            elif trntype=='DEPOSIT':
                if units:
                    assert unitprice==1
                    assert units>0
                    assert units*unitprice<=-total+0.02 # rounding error
                trntype='CREDIT'
                assetclass='DEPOSIT'
            else:
                raise Exception('Unknown TRNTYPE')


        strn=stagvalue('ACCTID',acctid,hashing=hashing)
        strn+=n3secid('CUSIP',secid)
        strn+=stagvalue('DTTRADE',dttrade)
        strn+=stagvalue('tran',trntype)
        strn+=stagvalue('units',units)
        print >>fout,stagvalue('FITID',hash(strn)),
        print >>fout,strn,
        print >>fout,stagvalue('DTASOF',dtasof),stagvalue('ACCTTYPE',accttype),
        print >>fout,stagvalue('DTSETTLE',dttrade),
        print >>fout,stagvalue('UNITPRICE',unitprice),
        print >>fout,stagvalue('TOTAL',total),
        print >>fout,stagvalue('CURRENCY',currency),
        print >>fout,stagvalue('ASSETCLASS',assetclass),
        #TODO print >>fout,stagvalue('RELSECID',relsecid),
        print >>fout,stagvalue('mEMO',memo,hashing=hashing)

        # Every transaction generates a matching transaction in the current account.
        # The only exception is a REINVEST transaction that for some reason has a TOTAL=0 in the CSV file but on the other hand
        # has a seperatet DEBIT transaction in the same CSV file.
        #if total and not secid.startswith('CASH='):
            #relsecid=secid
            #trntype='CREDIT' if total>0 else 'DEBIT'
            #secid='CASH='+currency
            #print >>fout,stagvalue('ACCTID',acctid,hashing=hashing),
            #print >>fout,stagvalue('ACCTTYPE','INVESTMENT'),
            #print >>fout,n3secid('CUSIP',secid),
            #print >>fout,stagvalue('DTSETTLE',dttrade),
            #print >>fout,stagvalue('DTTRADE',dttrade),
            #print >>fout,stagvalue('TRNTYPE',trntype),
            #print >>fout,stagvalue('UNITS',total),
            #print >>fout,stagvalue('UNITPRICE',1.),
            #print >>fout,stagvalue('TOTAL',total),
            #print >>fout,stagvalue('CURRENCY',currency),
            #print >>fout,stagvalue('RELSECID',relsecid),
        print >>fout,"];"
    print >>fout,"."
